﻿using System;

namespace Repository
{
    public static class SqlDefinitions
    {
        public static class Client
        {
            internal static string newClient =
            "INSERT INTO Clients(nameClient, surnameClient, nameCorporation, isProtected, descriptionClient,inventedName, phoneNumber, email, dateInsert) VALUES (@val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9)";

            internal static string updateClient =
           "update Clients (WITH ROWLOCK) set nameClient = @val1, surnameClient = @val2, nameCorporation = @val3, isProtected = @val4, descriptionClient = @val5, inventedName = @val6, phoneNumber = @val7, email = @val8, dateInsert = @val9 where idClient = @val11";

            internal static string clientDelete = "exec ClientDelete @val1";

            internal static string getClientList =
            "select idClient, nameClient, surnameClient, nameCorporation, isProtected, descriptionClient, inventedName, phoneNumber, email, dateInsert from Clients ";

            internal static string countResultByCriteria = "select count(1) from Clients where nameClient=@nameClient or surnameClient=@surnameClient or nameCorporation=@nameCorp";

            internal static string searchClient = "exec SearchClient @nameClient, @surnameClient, @inventedName, @nameCorp";
        }

        public static class Road
        {
            internal static string newRoad =
                "exec RoadNew @fromCity, @fromCountry, @toCity, @toCountry, @idClient, @idType, @value, @idCurrency";
            internal static string roadCQ = 
                "select r.idRoad, r.fromCountry, r.fromCity, r.toCountry, r.toCity, v.value, c.shortcut from Roads r INNER JOIN Clients c ON c.idClient=r.idClint INNER JOIN Valuations v ON v.idRoad=r.idRoad INNER JOIN Currency c ON c.idValuation = v.idCurrency";
        }

        public static class Valuation
        {
            internal static string newValuation =
                "insert into Valuations(idRoad, value, isActually, dateModyfi, idCurrency) values(@idRoad, @";
        }

        public static class TypeRoad
        {
        }

        public static class Currency
        {
        }
    }                 
}                     

                      
                      